/// <reference path="../../index.d.ts" />
const Service = require("egg").Service
let help = require("../extend/helper")
let ObjBase = require("obj-base")
class Count extends Service {
	constructor(ctx) {
		super(ctx)
		this.serverConfig = this.config.serverConfig
		this.ApplicationUser = this.app.model.ApplicationUser
	}
	/* 
	bug 渠道- 应用- 固定的注册人数,/授权人数
 */
	async countDateChannel({aid, channelId}) {
		var query = {aid, channelId}
		let sequelize = this.app.model
		let redisKey = `empire_channel_aid_count#${query.aid}#${query.channelId}`
		let obj = await this.ctx.service.db.getSet(redisKey, {
			noExist: async () => {
				let data = await sequelize.query(`SELECT date(application_user.create_at) AS dateList,
				COUNT(application_user.id) AS allUser
				 , SUM(IF(application_user.nickname IS NULL, 0, 1)) AS authUser,
				 invteCount.dayInvite,
				 clickCount.dayClick
			 FROM application_user
				 LEFT JOIN (
				 /*  子查询.邀请按日期统计 */
			 SELECT COUNT(empire_user.inviteid) as dayInvite,date(application_user.create_at) as create_at from application_user
						LEFT JOIN empire_user 
				 ON application_user.id = empire_user.inviteid AND date(application_user.create_at) = date(empire_user.create_at)
					 WHERE aid = :aid
				 AND application_user.channel = :channelId
					 GROUP  BY  date(application_user.create_at)
				 ) as invteCount on invteCount.create_at=date(application_user.create_at)
					 LEFT JOIN (
			 SELECT date(application_user.create_at) AS dateList,SUM(ad_channel_count.adClick) as dayClick  FROM ad_channel_count 
							 LEFT JOIN application_user on application_user.id=ad_channel_count.oid  AND ad_channel_count.date=date(application_user.create_at)
											 WHERE
												application_user.channel=:channelId
												 AND application_user.aid=:aid
						 GROUP BY date(application_user.create_at)
						 ) as clickCount on clickCount.dateList = date(application_user.create_at)
			 WHERE aid = :aid
				 AND application_user.channel = :channelId
			 GROUP BY date(application_user.create_at)
			 `, {
					type: sequelize.QueryTypes.SELECT, replacements: {
						aid: query.aid,
						channelId: query.channelId
					}
				})
				return {d: data, time: Date.now()}
			},
			/**
			 * @param {{d:Array,time:number}} dat
			 */
			deal: async (dat) => {
				return dat
			},
			expire: help.todayRemaining(),
		})
		return obj.data
	}
	/**
	 * {aid, channelId}以所有用户注册当天带来的广告
	 */
	/* 	async countRegAdclick({aid, channelId}){
		if (!aid || !channelId) {
			this.ctx.logger.warn("错误调用")
			return {}
		}
		let sequelize = this.app.model
		let date = await this.service.db.getSet(`countRegAdclickaid#${aid}#${channelId}`, {
			noExist: async function () {
				// 注册日期, 总点击量
				let queryRes = await sequelize.query(`SELECT date(application_user.create_at) AS dateList,SUM(ad_channel_count.adClick) as allClick  FROM ad_channel_count 
				LEFT JOIN application_user on application_user.id=ad_channel_count.oid WHERE application_user.channel=:channelId AND application_user.aid=:aid
			GROUP BY date(application_user.create_at)`, {logging: true, type: sequelize.QueryTypes.SELECT, replacements: {aid, channelId}})
				return queryRes
			}.bind(this),
			expire: help.todayRemaining()
		})
		return date.data
	} */

	/**
	 * 统计所有用户总带来的总点击量
	 * 按日期总统计, 所有广告
	 */
	async countUserallClick({aid, channelId}) {// 邀请
		if (!aid || !channelId) {
			this.ctx.logger.warn("错误调用")
			return {}
		}
		let sequelize = this.app.model
		let date = await this.service.db.getSet(`countUserallClick#${aid}#${channelId}`, {
			noExist: async function () {
				// 注册日期, 总点击量
				let queryRes = await sequelize.query(`SELECT ad_channel_count.date AS dateList,SUM(ad_channel_count.adClick) as allClick  FROM ad_channel_count WHERE  ad_channel_count.oid IN
				(SELECT application_user.id FROM  application_user WHERE application_user.channel=:channelId AND application_user.aid=:aid)
							GROUP BY ad_channel_count.date`, {logging: true, type: sequelize.QueryTypes.SELECT, replacements: {aid, channelId}})
				return queryRes
			}.bind(this),
			expire: help.todayRemaining()
		})
		return date.data
	}
	/**
	 * 统计所有用户总带来的总点击量
	 * 按日期总统计, 总邀请
	 */
	async countUserallInvite({aid, channelId}) {
		if (!aid || !channelId) {
			this.ctx.logger.warn("错误调用")
			return {}
		}
		let sequelize = this.app.model
		let date = await this.service.db.getSet(`countUserallInvite#${aid}#${channelId}`, {
			noExist: async function () {
				let queryRes = await sequelize.query(`
				SELECT date(empire_user.create_at) AS dateList,COUNT(empire_user.oid) AS allInviteid FROM empire_user WHERE empire_user.inviteid IN 
(SELECT application_user.id FROM  application_user WHERE application_user.channel=:channelId AND application_user.aid=:aid) 
GROUP BY date(empire_user.create_at)`, {logging: true, type: sequelize.QueryTypes.SELECT, replacements: {aid, channelId}})
				return queryRes
			}.bind(this),
			expire: help.todayRemaining()
		})
		return date.data
	}

	async countUserInviteAdClick({aid, channelId}) {
		let allClick = await this.countUserallClick({aid, channelId})
		let allInviteid = await this.countUserallInvite({aid, channelId})
		return ObjBase.arrayKeyConct([allClick, allInviteid], "dateList")
	}

	async getDateUser({aid, channelId, date}) {
		// 当天的用户
		if (!aid || !channelId || date) {
			this.ctx.logger.warn("错误调用")
			return []
		}
		// await sequelize.query("", {logging: false, type: sequelize.QueryTypes.SELECT, })
		let sequelize = this.app.model
		let dateBt = help.getToDay(new Date(date))

		await this.service.db.getSet(`app#dateUser#${aid}#${channelId}#${dateBt}`, {
			noExist: async () => {
				let date = await this.ApplicationUser.findAll({
					attributes: ["id"],
					where: {
						channel: channelId,
						aid: aid,
						[sequelize.Op.between]: [dateBt, dateBt + 3600 * 24 * 1000]
					}
				})
				return date
			},
			expire: 3600
		})
	}

	async getDateLevel({aid, channelId}) {// 十五日留存
		if (!aid || !channelId) {
			this.ctx.logger.warn("错误调用")
			return {}
		}
		let sequelize = this.app.model
		let date = await this.service.db.getSet(`#dateLevel${aid}#${channelId}`, {
			noExist: async () => {
				let date = await sequelize.query(`select sumdate as dateList,SUM(diff1) diff1,SUM(diff3) diff3,SUM(diff7) diff7,SUM(diff15) diff15
				from (
			 SELECT b.sumdate sumdate,a.oid,login_time,
					if(DATEDIFF(login_time,b.sumdate)=1,1,0) as diff1,
					if(DATEDIFF(login_time,b.sumdate)=3,1,0) as diff3, 
					if(DATEDIFF(login_time,b.sumdate)=7,1,0) as diff7,
					if(DATEDIFF(login_time,b.sumdate)=15,1,0) as diff15
					from user_login a, 
					 (
						SELECT Date(t.create_at) sumdate,t.id as new_user_guid FROM application_user t 
						WHERE t.channel=:channelId and t.aid=:aid
						GROUP BY DATE(t.create_at),t.id
				 )b
				 where a.oid =b.new_user_guid 
				 GROUP BY b.sumdate, a.oid,login_time ORDER BY a.oid,login_time
				)logdiffs
				GROUP BY sumdate `, {
					logging: false, type: sequelize.QueryTypes.SELECT, replacements: {
						aid, channelId
					}
				})
				return date
			},
			expire: help.todayRemaining()
		})
		return date.data
	}

	/**
	 * 这群人每天广告邀请的
	 */
	async getDayAdClick({aid, channelId, date}) {
		if (!aid || !channelId) {
			this.ctx.logger.warn("错误调用")
			return []
		}
		let sequelize = this.app.model
		let dataList = await this.service.db.getSet(`#dayAdClick_reg_date${aid}#${channelId}#${date}`, {
			noExist: async () => {
				let res = await sequelize.query(`SELECT SUM( ad_channel_count.adClick) as adClick,ad_channel_count.date as dateList FROM application_user
				INNER JOIN ad_channel_count on  ad_channel_count.oid=application_user.id
			 WHERE date(application_user.create_at) = :date
					 AND aid = :aid
					 AND application_user.channel = :channelId
							GROUP BY ad_channel_count.date`, {
					logging: false, type: sequelize.QueryTypes.SELECT, replacements: {
						aid, channelId, date
					}
				})
				return res
			},
			expire: help.todayRemaining()
		})
		return dataList.data
	}
	/**
	 * 获取这群人每日邀请
	 */
	async getDayInvite({aid, channelId, date}) {

		if (!aid || !channelId || !date) {
			this.ctx.logger.warn("错误调用")
			return []
		}
		let sequelize = this.app.model
		let dataList = await this.service.db.getSet(`#dayInvite_reg_date${aid}#${channelId}#${date}`, {
			noExist: async () => {
				let res = await sequelize.query(`SELECT  COUNT( empire_user.create_at) as userInvite, date(empire_user.create_at) as dateList 
				FROM application_user
					LEFT JOIN empire_user ON empire_user.inviteid = application_user.id
				WHERE date(application_user.create_at) = :date
					AND aid = :aid
					AND application_user.channel = :channelId
				GROUP BY date(empire_user.create_at)`, {
					logging: false, type: sequelize.QueryTypes.SELECT, replacements: {
						aid, channelId, date
					}
				})
				return res
			},
			expire: help.todayRemaining()
		})
		return dataList.data
	}
}
module.exports = Count